﻿/********************************************************************************
    Copyright (C) Binod Nepal, Planet Earth Solutions Pvt. Ltd., Kathmandu.
	Released under the terms of the GNU General Public License, GPL, 
	as published by the Free Software Foundation, either version 3 
	of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace MixNP.DatabaseLayer
{
    public static class Profiles
    {
        public static DataTable GetProfile(string emailAddress)
        {
            string sql = "SELECT * FROM dbo.UserProfileView WHERE EmailAddress=@EmailAddress AND Status=1;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                return Pes.DBFactory.DBOperations.GetDataTable(command);
            }
        }

        public static bool IsProfilePictureProtected(string emailAddress)
        {
            string sql = "SELECT '1' FROM dbo.Profiles WHERE EmailAddress=@EmailAddress AND ProtectProfilePicture=1 AND Status=1;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                return (Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command)).Equals("1"));
            }            
        }

        public static bool UpdateProfile(string emailAddress, bool protectProfilePicture, string sex, string name, string nickName, string cellNumber, bool displayCellNumber, string contactNumber, bool displayContactNumbers, string addressLine1, string addressLine2, string addressLine3, string city)
        {
            string sql = "dbo.UpdateProfile";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@ProtectProfilePicture", protectProfilePicture);
                command.Parameters.AddWithValue("@Sex", sex);
                command.Parameters.AddWithValue("@Name", name);
                command.Parameters.AddWithValue("@NickName", nickName);
                command.Parameters.AddWithValue("@CellNumber", cellNumber);
                command.Parameters.AddWithValue("@DisplayCellNumber", displayCellNumber);
                command.Parameters.AddWithValue("@ContactNumbers", contactNumber);
                command.Parameters.AddWithValue("@DisplayContactNumbers", displayContactNumbers);
                command.Parameters.AddWithValue("@AddressLine1", addressLine1);
                command.Parameters.AddWithValue("@AddressLine2", addressLine2);
                command.Parameters.AddWithValue("@AddressLine3", addressLine3);
                command.Parameters.AddWithValue("@City", city);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }
        
        }

        public static bool SaveProfilePicture(string emailAddress, string extension, byte[] picture)
        {
            string sql = "DELETE FROM dbo.ProfilePictures WHERE EmailAddress=@EmailAddress; INSERT INTO dbo.ProfilePictures(EmailAddress, Extension, Picture) SELECT @EmailAddress, @Extension, @Picture;";
            using (System.Data.SqlClient.SqlCommand command = new System.Data.SqlClient.SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@EmailAddress", emailAddress);
                command.Parameters.AddWithValue("@Extension", extension);
                command.Parameters.AddWithValue("@Picture", picture);

                return Pes.DBFactory.DBOperations.ExecuteNonQuery(command);
            }        
        }

        public static bool IsValidCellNumber(string cellNumber)
        {
            string sql = "SELECT TOP 1 1 FROM dbo.Profiles WHERE CellNumber=@CellNumber;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@CellNumber", cellNumber);

                return (Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command)) == "1");
            }
        }

        public static string GetEmailAddress(string cellNumber)
        {
            string sql = "SELECT EmailAddress FROM dbo.Profiles WHERE CellNumber=@CellNumber;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@CellNumber", cellNumber);

                return Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }        
        }
    }
}
